﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NPSoft.Ferrero.DataBridge.Model;
using NPSoft.Ferrero.Framework.DAL;

namespace NPSoft.Ferrero.DataBridge.DAL
{
    public class StoreUtil : IDAL<Store>
    {
        public int Add(Store info)
        {
            throw new NotImplementedException();
        }

        public int Edit(Store info)
        {
            throw new NotImplementedException();
        }

        public Store GetById(long id)
        {
            throw new NotImplementedException();
        }

        public Store GetById(Guid id)
        {
            throw new NotImplementedException();
        }

        public int Remove(Store info)
        {
            throw new NotImplementedException();
        }

        public System.Data.DataSet Search(StringBuilder condtion)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// SQL连接
        /// </summary>
        private SqlConnection Connection
        {
            get
            {
                return SqlHelper.GetConnection();
            }
        }

        public ICollection<Store> SearchCollection(StringBuilder condtion)
        {
            ICollection<Store> infos = new List<Store>();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT DISTINCT
                                MST_CUST.CUST_CD, MST_CUST.CUST_NAME, MST_CUST.CUST_NAME2, MST_CUST.CUST_CLUSTER,
                                MST_CUST.[2ND_DIST] AS SCD_DIST, MST_CUST.STORE_STATUS, dbo.LeftPad(MST_CUST.CUST_DIST_CD, '0', 10) AS CUST_DIST_CD,
                                MST_CUST.CUST_HIER1, MST_CUST.CUST_HIER2, MCH.CUSTHIER_DESC AS CUST_HIER3, MST_CUST.KEYACC_CD, MST_CUST.CUST_TYPE,
                                MST_CUST.CUST_ATTRIBUTE, SWH.CUST_CD AS SUBWAREHOUSE_CD, SWH.CUST_NAME AS SUB_CUST_NAME, MST_CUST.STORE_SQUARE,
                                MST_CUST.ADDR_1 + MST_CUST.ADDR_2 + MST_CUST.ADDR_3 + MST_CUST.ADDR_4 + MST_CUST.ADDR_5 AS ADDR,
                                MST_CUST.ADDR_POSTAL, MST_CUST.CONT_PR, MST_CUST.CONT_NO, MST_CUST.ADD_CONT_NO, MST_CUST.CONT_FAXNO,
                                MST_CUST.CONT_EMAIL, MST_CUST.USER_DEFINE1, MST_CUST.USER_DEFINE2, MST_CUST.USER_DEFINE3, MST_CUST.USER_DEFINE4,
                                MST_CUST.USER_DEFINE5, MST_CUST.HQ_DEFINE1, MST_CUST.HQ_DEFINE2, MST_CUST.HQ_DEFINE3, MST_CUST.HQ_DEFINE4,
                                MST_CUST.HQ_DEFINE5, MST_CUST.SR_TYPE, MST_CUST.STORE_ADMIN_DISTRICT, MST_CUST.CUST_STORE_CD,
                                MST_CUST.STORE_IS_FRANCHISE, MST_CUST.HAS_DISTRIBUTOR2, RATIO.RATIO, GEO.NODE_ID, RELATION.DST_CD,
                                RELATION.DISTRICT, RELATION.DST_NM_EN, RELATION.SR_EMP_ID, RELATION.SR_EMP_NM_CN, RELATION.SR_EMP_NM_EN,
                                RELATION.SR_USR_ID, RELATION.SV_EMP_ID, RELATION.SV_EMP_NM_CN, RELATION.SV_EMP_NM_EN, RELATION.SV_USR_ID,
                                RELATION.DSM_EMP_ID, RELATION.DSM_EMP_NM_CN, RELATION.DSM_EMP_NM_EN, RELATION.DSM_USR_ID, RELATION.CITY_CD,
                                RELATION.CITY, RELATION.CITY_SAP_CD, RELATION.CITY_NM_EN, RELATION.CITY_TYPE_LY, RELATION.CITY_TYPE_TY,
                                RELATION.CSM_EMP_ID, RELATION.CSM_EMP_NM_EN, RELATION.CSM_EMP_NM_CN, RELATION.CSM_USR_ID
                        FROM    MST_CUST
                        LEFT JOIN MST_SUBWH_STORE_RATIO AS RATIO
                        ON      MST_CUST.CUST_CD = RATIO.CUST_CD AND MST_CUST.SUBWAREHOUSE_CD = RATIO.SUBWH_CD
                                AND RATIO.STATUS = 4
                                AND DATEDIFF(MONTH, RATIO.VALID_MONTH, GETDATE()) = 0 AND RATIO.SYNCOPERATION <> 'D'
                        LEFT JOIN MST_CUST AS SWH
                        ON      RATIO.SUBWH_CD = SWH.CUST_CD
                                AND SWH.CUST_ATTRIBUTE = 'DC'
                        LEFT JOIN FW_GEO_NODE_CUST AS GEO
                        ON      MST_CUST.CUST_CD = GEO.CUST_CD
                                AND MST_CUST.CUST_ATTRIBUTE = 'Store'
                        LEFT JOIN uvw_EMP_SALORG_RELATION AS RELATION
                        ON      GEO.NODE_ID = RELATION.DST_NODE_ID
						LEFT JOIN MST_CUSTHIER MCH 
						ON MST_CUST.CUST_HIER3 = MCH.CUSTHIER_CD
								AND MCH.CUSTHIER_LEVEL = 3
								AND MCH.SYNCOPERATION <> 'D'");

            string sWhere = " where ";
            if (!string.IsNullOrEmpty(condtion.ToString()))
            {

                sql.Append(sWhere + condtion);
            }
            DataSet ds;
            ds = SqlHelper.ExecuteDataset(this.Connection, CommandType.Text, sql.ToString());
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                infos.Add(SetInfo(row));
            }
            return infos;
        }

        public Store SetInfo(System.Data.DataRow row)
        {
            Store store = new Store();
            store.ADD_CONT_NO = SqlHelper.GetString(row, "ADD_CONT_NO");
            store.ADDR = SqlHelper.GetString(row, "ADDR");
            store.ADDR_POSTAL = SqlHelper.GetString(row, "ADDR_POSTAL");
            store.STORE_STATUS = SqlHelper.GetString(row, "STORE_STATUS");
            store.CITY = SqlHelper.GetString(row, "CITY");
            store.CITY_CD = SqlHelper.GetString(row, "CITY_CD");
            store.CITY_NM_EN = SqlHelper.GetString(row, "CITY_NM_EN");
            store.CITY_SAP_CD = SqlHelper.GetString(row, "CITY_SAP_CD");
            store.CITY_TYPE_LY = SqlHelper.GetString(row, "CITY_TYPE_LY");
            store.CITY_TYPE_TY = SqlHelper.GetString(row, "CITY_TYPE_TY");
            store.CONT_EMAIL = SqlHelper.GetString(row, "CONT_EMAIL");
            store.CONT_FAXNO = SqlHelper.GetString(row, "CONT_FAXNO");
            store.CONT_NO = SqlHelper.GetString(row, "CONT_NO");
            store.CONT_PR = SqlHelper.GetString(row, "CONT_PR");
            store.CSM_EMP_ID = SqlHelper.GetString(row, "CSM_EMP_ID");
            store.CSM_EMP_NM_CN = SqlHelper.GetString(row, "CSM_EMP_NM_CN");
            store.CSM_EMP_NM_EN = SqlHelper.GetString(row, "CSM_EMP_NM_EN");
            store.CSM_USR_ID = SqlHelper.GetString(row, "CSM_USR_ID");
            store.CUST_ATTRIBUTE = SqlHelper.GetString(row, "CUST_ATTRIBUTE");
            store.CUST_CD = SqlHelper.GetString(row, "CUST_CD");
            store.CUST_CLUSTER = SqlHelper.GetString(row, "CUST_CLUSTER");
            store.CUST_DIST_CD = SqlHelper.GetString(row, "CUST_DIST_CD");
            store.CUST_HIER1 = SqlHelper.GetString(row, "CUST_HIER1");
            store.CUST_HIER2 = SqlHelper.GetString(row, "CUST_HIER2");
            store.CUST_HIER3 = SqlHelper.GetString(row, "CUST_HIER3");
            store.CUST_NAME = SqlHelper.GetString(row, "CUST_NAME");
            store.CUST_NAME2 = SqlHelper.GetString(row, "CUST_NAME2");
            store.CUST_TYPE = SqlHelper.GetString(row, "CUST_TYPE");
            store.DISTRICT = SqlHelper.GetString(row, "DISTRICT");
            store.DSM_EMP_ID = SqlHelper.GetString(row, "DSM_EMP_ID");
            store.DSM_EMP_NM_CN = SqlHelper.GetString(row, "DSM_EMP_NM_CN");
            store.DSM_EMP_NM_EN = SqlHelper.GetString(row, "DSM_EMP_NM_EN");
            store.DSM_USR_ID = SqlHelper.GetString(row, "DSM_USR_ID");
            store.DST_CD = SqlHelper.GetString(row, "DST_CD");
            store.DST_NM_EN = SqlHelper.GetString(row, "DST_NM_EN");
            store.HAS_DISTRIBUTOR2 = SqlHelper.GetString(row, "HAS_DISTRIBUTOR2");
            store.HQ_DEFINE1 = SqlHelper.GetString(row, "HQ_DEFINE1");
            store.HQ_DEFINE2 = SqlHelper.GetString(row, "HQ_DEFINE2");
            store.HQ_DEFINE3 = SqlHelper.GetString(row, "HQ_DEFINE3");
            store.HQ_DEFINE4 = SqlHelper.GetString(row, "HQ_DEFINE4");
            store.HQ_DEFINE5 = SqlHelper.GetString(row, "HQ_DEFINE5");
            store.KEYACC_CD = SqlHelper.GetString(row, "KEYACC_CD");
            store.RATIO = SqlHelper.GetString(row, "RATIO");
            store.SECOUND_DIST = SqlHelper.GetString(row, "SCD_DIST");
            store.SR_EMP_ID = SqlHelper.GetString(row, "SR_EMP_ID");
            store.SR_EMP_NM_CN = SqlHelper.GetString(row, "SR_EMP_NM_CN");
            store.SR_EMP_NM_EN = SqlHelper.GetString(row, "SR_EMP_NM_EN");
            store.SR_TYPE = SqlHelper.GetString(row, "SR_TYPE");
            store.SR_USR_ID = SqlHelper.GetString(row, "SR_USR_ID");
            store.STORE_ADMIN_DISTRICT = SqlHelper.GetString(row, "STORE_ADMIN_DISTRICT");
            store.CUST_STORE_CD = SqlHelper.GetString(row, "CUST_STORE_CD");
            store.STORE_IS_FRANCHISE = SqlHelper.GetString(row, "STORE_IS_FRANCHISE");
            store.STORE_SQUARE = SqlHelper.GetString(row, "STORE_SQUARE");
            store.SUB_CUST_NAME = SqlHelper.GetString(row, "SUB_CUST_NAME");
            store.SUBWAREHOUSE_CD = SqlHelper.GetString(row, "SUBWAREHOUSE_CD");
            store.SV_EMP_ID = SqlHelper.GetString(row, "SV_EMP_ID");
            store.SV_EMP_NM_CN = SqlHelper.GetString(row, "SV_EMP_NM_CN");
            store.SV_EMP_NM_EN = SqlHelper.GetString(row, "SV_EMP_NM_EN");
            store.SV_USR_ID = SqlHelper.GetString(row, "SV_USR_ID");
            store.USER_DEFINE1 = SqlHelper.GetString(row, "USER_DEFINE1");
            store.USER_DEFINE2 = SqlHelper.GetString(row, "USER_DEFINE2");
            store.USER_DEFINE3 = SqlHelper.GetString(row, "USER_DEFINE3");
            store.USER_DEFINE4 = SqlHelper.GetString(row, "USER_DEFINE4");
            store.USER_DEFINE5 = SqlHelper.GetString(row, "USER_DEFINE5");

            return store;
        }
    }
}
